﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using LibHuman;

namespace Human
{
    public partial class frm_bcnltxtheonhomngach : Form
    {
        AccessData mDb = new AccessData();
        Language lan = new Language();
        ExportToExcel exp = new ExportToExcel();
        string s_bophan="",user_dn="", tungay = "", denngay = "", ngay = "", ReportName = "", msg = "", title = "", s_manhom = "", s_tennhom = "", s_mmyy = "";
        DataSet ds;
        DataSet ds_nhomn = new DataSet();
        public frm_bcnltxtheonhomngach(string s_makp,string s_userdn)
        {
            InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            s_bophan = s_makp;
            user_dn = s_userdn;
        }
        
        private void frm_bcnangbacluongdungthoihan_Load(object sender, EventArgs e)
        {
            load_nhomngach();
        }

        private void btnxem_Click(object sender, EventArgs e)
        {
            getngay();
            ds = getdata();
            ds.WriteXml("..\\xml\\h_bcnangluongthuongxuyentheonhomngach.xml", XmlWriteMode.WriteSchema);
            if (ds != null && ds.Tables["Table1"].Rows.Count > 0)
            {
                ReportName = "h_bcnangluongthuongxuyentheonhomngach.rpt";
                title = lan.Change_language_MessageText("Ngày ") + DateTime.Now.Day.ToString().PadLeft(2, '0');
                title +=lan.Change_language_MessageText( " tháng ") + DateTime.Now.Month.ToString().PadLeft(2, '0');
                title += lan.Change_language_MessageText(" năm ") + DateTime.Now.Year.ToString();

                frmReport f = new frmReport(mDb, ds, ReportName, title, msg,user_dn);
                f.Show();
            }
            else
                MessageBox.Show(lan.Change_language_MessageText("Không có dữ liệu!"),AccessData.Msg);
        }
        private void load_nhomngach()
        {
            ds_nhomn = mDb.get_nhomngach();
            foreach (DataRow dr in ds_nhomn.Tables[0].Rows)
            {
                chkl_nhomngach.Items.Add(dr["tennhom"].ToString());
            }
        }
        public DataSet getdata()
        {
            getngay();
            string nam = denngay.Substring(6,4);
            try
            {
                string sql = " select count(distinct l.manv) as nv_cuoiky,";
                sql += " sum(case when to_date(to_char(b.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy')";
                sql += " between to_date('" + tungay + "','dd/mm/yyyy') and to_date('" + denngay + "','dd/mm/yyyy')";
                sql += " and (case when mn.loaingach in ('A0','A1','A2','A3')";
                sql += " then EXTRACT(year from age(to_timestamp(b.ngaynangbac,'yyyy-mm'),to_timestamp(b.ngaygiubac,'yyyy-mm'))) else 0 end =3 ";
                sql += " or case when mn.loaingach in ('B','C') then EXTRACT(year from age(to_timestamp(b.ngaynangbac,'yyyy-mm'),to_timestamp(b.ngaygiubac,'yyyy-mm'))) else 0 end =2)";
                sql += " then 1 else 0 end) as nv_nbacluong";
                sql += " ,sum(case when to_timestamp(to_char(b.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy') between to_timestamp('" + tungay + "','dd/mm/yyyy') and to_timestamp('" + denngay + "','dd/mm/yyyy')";
                sql += "  and ((case when mn.loaingach in ('A0','A1','A2','A3')";
                sql += " then EXTRACT(year from age(to_timestamp(b.ngaynangbac,'yyyy-mm'),to_timestamp(b.ngaygiubac,'yyyy-mm'))) else 0 end >=2 and";
                sql += " case when mn.loaingach in ('A0','A1','A2','A3') then EXTRACT(year from age(to_timestamp(b.ngaynangbac,'yyyy-mm'),to_timestamp(b.ngaygiubac,'yyyy-mm'))) else 0 end <3)";
                sql += " or (case when mn.loaingach in ('B','C')";
                sql += " then EXTRACT(year from age(to_timestamp(b.ngaynangbac,'yyyy-mm'),to_timestamp(b.ngaygiubac,'yyyy-mm'))) else 0 end >=1";
                sql += "  and case when mn.loaingach in ('B','C') ";
                sql += " then EXTRACT(year from age(to_timestamp(b.ngaynangbac,'yyyy-mm'),to_timestamp(b.ngaygiubac,'yyyy-mm'))) else 0 end  < 2 ))";
                sql += " and h.tbnghihuu=0 then 1 else 0 end) as nv_nbactruoc";
                sql += " ,sum(case when to_timestamp(to_char(b.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy') between to_timestamp('" + tungay + "','dd/mm/yyyy') and to_timestamp('" + denngay + "','dd/mm/yyyy')";
                sql += "  and ((case when mn.loaingach in ('A0','A1','A2','A3')";
                sql += " then EXTRACT(year from age(to_timestamp(b.ngaynangbac,'yyyy-mm'),to_timestamp(b.ngaygiubac,'yyyy-mm'))) else 0 end >=2 and";
                sql += " case when mn.loaingach in ('A0','A1','A2','A3') then EXTRACT(year from age(to_timestamp(b.ngaynangbac,'yyyy-mm'),to_timestamp(b.ngaygiubac,'yyyy-mm'))) else 0 end <3)";
                sql += " or (case when mn.loaingach in ('B','C')";
                sql += " then EXTRACT(year from age(to_timestamp(b.ngaynangbac,'yyyy-mm'),to_timestamp(b.ngaygiubac,'yyyy-mm'))) else 0 end >=1";
                sql += "  and case when mn.loaingach in ('B','C') ";
                sql += " then EXTRACT(year from age(to_timestamp(b.ngaynangbac,'yyyy-mm'),to_timestamp(b.ngaygiubac,'yyyy-mm'))) else 0 end  < 2 ))";
                sql += " and h.tbnghihuu=1 then 1 else 0 end) as nv_nangbactruocthoihan";
                sql += " from " + mDb.user + ".hopdong h inner join " + mDb.user + ".llnv l on h.manv=l.manv  left join " + mDb.user + ".thaydoi_bluong b on b.manv=h.manv";
                sql += " inner join " + mDb.user + ".mangach mn on h.mangach=mn.ma inner join " + mDb.user + ".max_lan ml on h.manv=ml.manv ";
                sql += " where to_date(to_char(h.ngaydenct,'dd/mm/yyyy'),'dd/mm/yyyy') <= to_date('" + denngay + "','dd/mm/yyyy')";
                sql += " and (to_date(to_char(h.ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy')>= to_date('" + denngay + "','dd/mm/yyyy') or h.ngaynghi is null) and h.lan=ml.lan";

                if (s_manhom.Trim() != "")
                {
                    string s1 = g_manhom(s_manhom.Substring(0, s_manhom.Trim().Length - 1));
                    sql += " and mn.nhomngach in (" + s1.Substring(0, s1.Length - 1) + ")";
                }

                string sql1 = " select distinct a.manv,a.ho||' '||a.ten as hoten,0 as stt, case when a.phai='Nam' then to_char(a.ngaysinh,'yyyy') end as nam,bp.tenbp,";
                sql1 += " case when a.phai='Nam' then to_char(a.ngaysinh,'dd/mm/yyyy') end as ngaysinhnam,trim(ct.tenctdt)||' - '||trim(bc.tenbang) as td_chuyenmon,b.ngay as ngayud, case when a.phai<>'Nam' then to_char(a.ngaysinh,'yyyy') end as nu,";
                sql1 += " case when a.phai<>'Nam' then to_char(a.ngaysinh,'dd/mm/yyyy') end as ngaysinhnu,m.loaingach, b.mangachcu,trim(substr(trim(b.bacluongcu),1,2)) as bacluongcu,";
                sql1 += " to_number(trim(substr(trim(b.bacluongcu),6))) as hs_lc_cu,to_char(b.ngaygiubac,'dd/mm/yyyy') as ngaygiubac,0.0 as hs_bluu,b.mangachmoi,trim(substr(trim(b.bacluongmoi),1,2)) as bacluongmoi,";
                sql1 += " to_number(trim(substr(trim(b.bacluongmoi),6))) as hs_lc_moi,to_char(b.ngaynangbac,'dd/mm/yyyy') as ngaynangbac,0.0 as hs_bluu_moi,h.luonghd,'' as ghichu,0.0 as st_chenhlech,'' as hsvk_cu,'' as hsvk_moi,'" + nam + "' as namdn,";
                sql1 += " (to_number(trim(substr(trim(b.bacluongmoi),6))) - to_number(trim(substr(trim(b.bacluongcu),6)))) as hs_chenhlech,b.ngay,EXTRACT(month from age(to_timestamp(b.ngay,'yyyy-mm-dd'),to_timestamp(b.ngaynangbac,'yyyy-mm-dd'))) as sothang";
                sql1 += " from " + mDb.user + ".llnv a inner join " + mDb.user + ".hopdong h on h.manv=a.manv  inner join " + mDb.user + ".thaydoi_bluong b on b.manv=a.manv";
                sql1 += " join " + mDb.user + ".mangach m on m.ma1=b.mangachcu  inner join " + mDb.user + ".max_lan ml on h.manv=ml.manv";
                sql1 += "  left join " + mDb.user + ".ctdaotao ct on ct.mactdt=a.nnghiep left join " + mDb.user + ".bangcap bc on a.td_nnghiep=bc.mabangcap";
                sql1 += " left join " + mDb.user + ".bophan bp on a.madv=bp.madv";
                sql1 += " where to_timestamp(to_char(b.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy')";
                sql1 += " between to_timestamp('" + tungay + "','dd/mm/yyyy')  and to_timestamp('" + denngay + "','dd/mm/yyyy')  and trim(substr(trim(b.bacluongmoi),1,2))<>'1' ";
                sql1 += " and (h.ngaynghi is null or to_timestamp(to_char(h.ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy')>to_timestamp('" + denngay + "','dd/mm/yyyy')) and h.lan=ml.lan  ";
                sql1 += " and (m.loaingach in ('A0','A1','A2','A3') and  extract(year from age(to_timestamp(to_char(b.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy'),";
                sql1 += " to_timestamp(to_char(b.ngaygiubac,'dd/mm/yyyy'),'dd/mm/yyyy')))=3";
                sql1 += " or m.loaingach in ('B','C') and extract(year from age(to_timestamp(to_char(b.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy'),to_timestamp(to_char(b.ngaygiubac,'dd/mm/yyyy'),'dd/mm/yyyy'))) =2)";
                if (s_manhom.Trim() != "")
                {
                 string s1 = g_manhom(s_manhom.Substring(0, s_manhom.Trim().Length - 1));
                 sql1 += " and m.nhomngach in (" + s1.Substring(0, s1.Length - 1) + ")";
                }
                ds = mDb.get_data(sql, sql1);

                foreach (DataRow dr in ds.Tables[1].Rows)
                {
                    decimal hs_chenhlech = 0, sothang = 0, st_chenhlech = 0, luong_hd = 0;
                    hs_chenhlech = decimal.Parse(dr["hs_chenhlech"].ToString());
                    sothang = decimal.Parse(dr["sothang"].ToString());
                    luong_hd = decimal.Parse(dr["luonghd"].ToString());
                    st_chenhlech = hs_chenhlech * sothang * luong_hd;
                    dr["st_chenhlech"] = st_chenhlech.ToString();//"###.###"
                }            
            }
            catch
            {
                return null;
            }
            return ds;
        }
        private string g_manhom(string str)
        {
            string s = "";
            if (str.Trim().Length > 0)
            {
                string[] s1 = str.Split(',');
                for (int i = 0; i < s1.Length; i++)
                {
                    s = s + "'" + s1[i].ToString().Trim() + "',";
                }
            }
            return s;
        }        
        public void getngay()
        {
            tungay = haison1.tungay;
            denngay = haison1.denngay;
            s_mmyy = denngay.Substring(3, 2).ToString() + denngay.Substring(8, 2).ToString();
            msg = lan.Change_language_MessageText("DANH SÁCH NGẠCH VIÊN CHỨC ")+ txt_tieude.Text.ToUpper().ToString() +lan.Change_language_MessageText(" NÂNG LƯƠNG THƯỜNG XUYÊN TỪ NGÀY   ") +" "+ tungay + " " + lan.Change_language_MessageText("  ĐẾN NGÀY  ") + " " + denngay;
        }
        public DataSet exp_exel(DataSet dse)
        {

            DataSet dse1 = new DataSet();
            dse1.Tables.Add();
            dse1.Tables[0].Columns.Add("stt", typeof(string));
            dse1.Tables[0].Columns.Add("hoten", typeof(string));
            dse1.Tables[0].Columns.Add("ngaysinhnam", typeof(string));
            dse1.Tables[0].Columns.Add("ngaysinhnu", typeof(string));
            dse1.Tables[0].Columns.Add("td_chuyenmon", typeof(string));
            dse1.Tables[0].Columns.Add("tenbp", typeof(string));
            dse1.Tables[0].Columns.Add("mangachcu", typeof(string));
            dse1.Tables[0].Columns.Add("bacluongcu", typeof(string));
            dse1.Tables[0].Columns.Add("hs_lc_cu", typeof(string));
            dse1.Tables[0].Columns.Add("ngaygiubac", typeof(string));
            dse1.Tables[0].Columns.Add("hs_bluu", typeof(string));
            dse1.Tables[0].Columns.Add("mangachmoi", typeof(string));
            dse1.Tables[0].Columns.Add("bacluongmoi", typeof(string));
            dse1.Tables[0].Columns.Add("hs_lc_moi", typeof(string));
            dse1.Tables[0].Columns.Add("ngaynangbac", typeof(string));
            dse1.Tables[0].Columns.Add("hs_bluu_moi", typeof(string));
            dse1.Tables[0].Columns.Add("hs_chenhlech", typeof(string));

            dse1.Tables[0].Columns.Add("sothang", typeof(string));
            dse1.Tables[0].Columns.Add("st_chenhlech", typeof(string));
            dse1.Tables[0].Columns.Add("ghichu", typeof(string));

            int stt = 0;
            dse1.Tables.Add();
            dse1.Tables[1].Columns.Add("nv_cuoiky", typeof(string));//
            dse1.Tables[1].Columns.Add("nv_nbacluong", typeof(string));
            dse1.Tables[1].Columns.Add("nv_nbactruoc", typeof(string));
            dse1.Tables[1].Columns.Add("nv_nangbactruocthoihan", typeof(string));
            DataRow nrow;
            foreach (DataRow r in dse.Tables[1].Rows)
            {
                stt++;
                nrow = dse1.Tables[0].NewRow();
                nrow["stt"] = stt.ToString();
                nrow["hoten"] = r["hoten"].ToString();
                nrow["ngaysinhnam"] = r["ngaysinhnam"].ToString();
                nrow["ngaysinhnu"] = r["ngaysinhnu"].ToString();
                nrow["td_chuyenmon"] = r["td_chuyenmon"].ToString();
                nrow["tenbp"] = r["tenbp"].ToString();
                nrow["mangachcu"] = r["mangachcu"].ToString();
                nrow["bacluongcu"] = r["bacluongcu"].ToString();
                nrow["hs_lc_cu"] = r["hs_lc_cu"].ToString();

                nrow["ngaygiubac"] = r["ngaygiubac"].ToString();
                nrow["hs_bluu"] = r["hs_bluu"].ToString();
                nrow["mangachmoi"] = r["mangachmoi"].ToString();
                nrow["bacluongmoi"] = r["bacluongmoi"].ToString();
                nrow["hs_lc_moi"] = r["hs_lc_moi"].ToString();

                nrow["ngaynangbac"] = r["ngaynangbac"].ToString();
                nrow["hs_chenhlech"] = r["hs_chenhlech"].ToString();
                nrow["hs_bluu_moi"] = r["hs_bluu_moi"].ToString();
                nrow["sothang"] = r["sothang"].ToString();
                nrow["st_chenhlech"] = r["st_chenhlech"].ToString();
                nrow["ghichu"] = r["ghichu"].ToString();
                dse1.Tables[0].Rows.Add(nrow);
                //

            }
            DataRow nrow1;
            foreach (DataRow r1 in dse.Tables[0].Rows)
            {
                nrow1 = dse1.Tables[1].NewRow();
                nrow1["nv_cuoiky"] = r1["nv_cuoiky"].ToString();
                nrow1["nv_nbacluong"] = r1["nv_nbacluong"].ToString();
                nrow1["nv_nbactruoc"] = r1["nv_nbactruoc"].ToString();
                nrow1["nv_nangbactruocthoihan"] = r1["nv_nangbactruocthoihan"].ToString();
                dse1.Tables[1].Rows.Add(nrow1);
            }
            return dse1;
        }

        private void btn_exel_Click(object sender, EventArgs e)
        {
            string nam = haison1.denngay.Substring(6,4);
           
            getdata();
            DataSet ds1 = exp_exel(ds);
            exp.exp_bc_nltxtheonhomngach(ds1, "bc_nltxtheonhomngach", msg, nam);
        }

        private void btnketthuc_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void haison1_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
        }

        private void btnxem_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
        }

        private void btn_exel_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
        }

        private void chkl_nhomngach_Validated(object sender, EventArgs e)
        {
            try
            {
                chkl_nhomngach.SetItemCheckState(chkl_nhomngach.SelectedIndex, CheckState.Checked);
                s_manhom = ""; s_tennhom = "";
                for (int i = 0; i < chkl_nhomngach.Items.Count; i++)
                {
                    if (chkl_nhomngach.GetItemChecked(i))
                    {
                        s_manhom += mDb.getrowbyid(ds_nhomn.Tables[0], "tennhom='" + chkl_nhomngach.Items[i].ToString() + "'")["manhom"].ToString() + ",";
                        s_tennhom += chkl_nhomngach.Items[i].ToString() + ",";
                    }
                }
                s_tennhom = s_tennhom.Trim().Substring(0, s_tennhom.Length - 1);
                txt_tieude.Text = s_tennhom;
            }
            catch { }
        }
    }
}